﻿using Microsoft.AspNetCore.Mvc;
using MVC_Demo.Models;
using MVC_Demo.Models.DataBase;
using System;
using System.Collections.Generic;
using System.Linq;

namespace MVC_Demo.Controllers
{
    public class TestController : Controller
    {

        private readonly McStoreContext _db;
        public TestController(McStoreContext db)
        {
            _db = db;
        }

        public IActionResult Index()
        {

            return View();
        }
        [Route("/myview")]
        public IActionResult MyView()
        {
            return View();
        }

        public IActionResult ViewDataDemo()
        {
            ViewData["name"] = "ken";
            ViewData["birthday"] = new DateTime(2000, 1, 1);
            ViewData["hobby"] = new string[] { "跑步", "阅读", "Coding" };
            return View();
        }
        public IActionResult ViewBagDemo()
        {
            ViewBag.Title = "ViewBag传值示例";
            ViewBag.Name = "ken";
            ViewBag.Birthday = new DateTime(2000, 1, 1);
            ViewBag.Hobby = new string[] { "跑步", "阅读", "Coding" };
            return View();
        }
        public IActionResult ViewModelDemo()
        {
            ViewBag.Title = "ViewModel传值示例";
            var person = new Person
            {
                Name = "ken",
                Birthday = new DateTime(2000, 1, 1),
                Hobby = new string[] { "跑步", "阅读", "Coding" }
            };
            //等同于 return View("ViewModelDemo", person);
            return View(person);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IActionResult ViewModeDemo()
        {
            ViewBag.name = "张飞";

            ViewBag.date = new DateTime(2022, 09, 24);
            return View();
        }
        //public IActionResult ViewMode1Demo()
        // {
        //var peron = new Person();
        // peron.Name = "关羽";
        //peron.Number = 2;
        //peron.date = new DateTime(2022, 09, 23);
        // return View(person);
        // }
        
        public IActionResult UsingDbContext()
        {
            using (var db = new McStoreContext())
            {
            }
            return new EmptyResult();
        }

        public IActionResult Add()
        {
            //new一个分类对象 等同于数据
            var cate = new Category()
            {
                CateName = "手机"
            };

            //添加数据
            _db.Categories.Add(cate);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("添加成功");
            return Content("添加失败");
        }

        public IActionResult AddRange()
        {
            //new一个对象列表
            var list = new List<Category>();
            list.Add(new Category
            {
                CateName = "家具"
            });
            list.Add(new Category
            {
                CateName = "家电"
            });
            list.Add(new Category
            {
                CateName = "图书"
            });
            //添加数据
            _db.Categories.AddRange(list);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("添加成功");
            return Content("添加失败");
        }


        public IActionResult GetList()
        {
            var list = _db.Categories.Where(x => x.CateName == "手机").ToList();
            return new ObjectResult(list);
        }


        public IActionResult Update()
        {
            //查询Id为1的数据
            var model = _db.Categories.FirstOrDefault(x => x.Id == 1);

            //如果没有数据 返回提示
            if (model == null)
            {
                return Content("数据不存在");
            }

            //赋值
            model.CateName = "手机（更新）";

            //更新
            _db.Categories.Update(model);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("更新成功");
            return Content("更新失败");
        }

        public IActionResult UpdateRange()
        {
            //查询Id为1的数据
            var list = _db.Categories.ToList();

            foreach (var item in list)
            {
                item.CateName = item.CateName + "（更新）";
            }

            //更新
            _db.Categories.UpdateRange(list);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("更新成功");
            return Content("更新失败");
        }

        public IActionResult Remove()
        {
            //查询Id为1的数据
            var model = _db.Categories.FirstOrDefault(x => x.Id == 1);

            //如果没有数据 返回提示
            if (model == null)
            {
                return Content("数据不存在");
            }

            //删除
            _db.Categories.Remove(model);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("删除成功");
            return Content("删除失败");
        }

        public IActionResult RemoveRange()
        {
            //把包含“家”的分类查出来
            var list = _db.Categories.Where(x => x.CateName.Contains("家")).ToList();

            //删除
            _db.Categories.RemoveRange(list);

            //保存 执行SQL
            var row = _db.SaveChanges();

            //如果影响行数>0
            if (row > 0)
                return Content("删除成功");
            return Content("删除失败");
        }

        public IActionResult Sort()
        {
            //正序排序
            var list1 = _db.Goods.OrderBy(x => x.Price).ToList();
            //倒序排序
            var list2 = _db.Goods.OrderByDescending(x => x.Price).ToList();
            //混合排序
            var list3 = _db.Goods.OrderBy(x => x.Price).ThenBy(x => x.Id).ToList();
            return new ObjectResult(list3);
        }

        public IActionResult SkipAndTake()
        {
            //取价格从低到高的前5个
            var list = _db.Goods.OrderBy(x => x.Price)
                .Take(5)
                .ToList();

            //取价格从低到高的6-10
            list = _db.Goods.OrderBy(x => x.Price)
                .Skip(5)
                .Take(5)
                .ToList();
            return new ObjectResult(list);
        }

        public IActionResult Any()
        {
            //判断数据是否存在
            var result = _db.Goods.Any();
            //判断分类Id等于3的数据是否存在
            result = _db.Goods.Any(x => x.CateId == 3);
            return new ObjectResult(result);
        }
        public IActionResult Count()
        {
            //获取数据的数量
            var result = _db.Goods.Count();
            //带条件获取数据的数量
            result = _db.Goods.Count(x => x.CateId == 3);
            return new ObjectResult(result);
        }

        public IActionResult Sum()
        {
            //获取数据的求和总数
            var total = _db.Goods.Sum(x => x.Price);
            //带条件获取数据的数量
            total = _db.Goods.Where(x => x.CateId == 1).Sum(x => x.Price);
            return new ObjectResult(total);
        }

        public IActionResult GroupBy()
        {
            //分组统计每个分类下的商品数量
            var list1 = _db.Goods.GroupBy(x => x.CateId).Select(x => new
            {
                x.Key,
                Count = x.Count(),
            });

            //分组统计每个分类下的商品总金额
            var list2 = _db.Goods.GroupBy(x => x.CateId).Select(x => new
            {
                x.Key,
                Sum = x.Sum(y => y.Price)
            });
            return new ObjectResult(list1);
        }

        public IActionResult Join()
        {
            //查询商品并连接商品分类表（一对一）
            var list1 = _db.Goods.Join(_db.Categories, x => x.CateId, y => y.Id, (x, y) => new
            {
                x.Id,
                x.Price,
                x.Name,
                x.Cover,
                y.CateName,
            }).ToList();

            //查询分类并连接商品表（一对多）
            var list2 = _db.Categories.GroupJoin(_db.Goods, x => x.Id, y => y.CateId, (x, y) => new
            {
                x.CateName,
                x.Id,
                GoodList = y.ToList()
            }).ToList();
            return new ObjectResult(list1);
        }
    }
}
